

* Input:
* Data/inventory.dta
* Data/accounts_panel.dta
* Data/invoice_year.dta


* Output:
* Data/accounts_inventory_panel.dta
* Data/accinvent_invoice_panel.dta



set more off
********************************************************************
********************* INVENTORY AND ACCOUNT DATA - PANEL ***********
********************************************************************
use Data/inventory.dta, clear

sort account_id date_start


collapse (firstnm) date_end date_start rating_plan product_code product_desc (max) FIT_product ,  by(account_id date_start_month) 

rename date_start_month contract_start_month

sort account_id contract_start_month


merge 1:1 account_id contract_start_month using Data/accounts_panel.dta


by account_id, sort: egen max_merge_inventoryaccounts =max(_merge)


rename _merge merge_inventoryaccounts

sort account_id contract_start_month

order account_id contract_start_month contract_end_month date_end solar FIT feedin green

by account_id, sort: egen min_datestart = min(date_start)
by account_id, sort: egen max_dateend = max(date_end)

format min_date %td
format max_date %td

gen min_monthstart= mofd(date_start)

replace contract_start_month = min_monthstart if contract_start_month ==.

save Data/accounts_inventory_panel.dta, replace





********************************************************************
***************** INVENTORY AND ACCOUNT DATA TO INVOICE DATA********
********************************************************************



use Data/accounts_inventory_panel.dta, clear


capture drop year

gen year = year(date_start)


** prices change yearly 
joinby account_id year using Data/invoice_year.dta, unmatched(both) _merge(merge_accinvent_invoice)


sort account_id year contract_start_month

** incomplete invoice data pre 2013

gen offer_discount = 0
forval p = 1(1)50 {
	replace offer_discount = `p' if strpos(offer_name,"`p'%")>0
	
	}
	

* use other customers on same network tariff to fill in missing prices
by product_code year, sort: egen mean_weighted_price = mean(weighted)  


gen discounted_price = mean_weighted*(1 - (offer_discount/100))

** average price if people did not choose the green plan
by product_code year, sort: egen mean_discounted_nongreen = mean(discounted_price) if green_plan ==0
by product_code year, sort: egen min_discounted_nongreen = min(mean_discounted_nongreen) 
replace mean_discounted_nongreen = min_discounted_nongreen if mean_discounted_nongreen ==.

drop min_discounted_nongreen


* keep only accounts/inventory data
drop if merge_accinvent == 2 


save Data/accinvent_invoice_panel.dta, replace


